package org.ysh.demo.poi;

import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

public class ExcelReaderDemo {

    public static void main(String[] args) {
        String excelPath = "C:\\Users\\hxs_2\\Desktop\\文档\\桥 ok.xlsx";
        readArchiveNo(excelPath);
//        readExcelDate();
    }

    public static void readExcelDate() {
        String excelPath = "C:\\Users\\hxs_2\\Desktop\\拆迁征地0001.xlsx";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

//        DateFormat df1 = DateFormat.getDateInstance(DateFormat.SHORT, Locale.CHINA);

        // 指定excel文件，创建缓存输入流
        try(BufferedInputStream inputStream = new BufferedInputStream(new FileInputStream(excelPath))){
            // 直接传入输入流即可，此时excel就已经解析了
            try(XSSFWorkbook workbook = new XSSFWorkbook(inputStream)){
                // 选择要处理的sheet名称
                XSSFSheet sheet = workbook.getSheetAt(0);

                // 第一行表头，单独处理

                // 迭代遍历sheet剩余的每一行
                for (int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                    if (rowNum == 0) { // 读取第一行（表头）
//                        XSSFRow head = sheet.getRow(rowNum);
//                        String headColumn_1 = head.getCell(0).getStringCellValue();
//                        String headColumn_2 = head.getCell(1).getStringCellValue();
//                        String headColumn_3 = head.getCell(2).getStringCellValue();
//                        String headColumn_4 = head.getCell(3).getStringCellValue();
//                        String headStr = String.format("%s\t%s\t%s\t%s", headColumn_1, headColumn_2, headColumn_3, headColumn_4);
//                        System.out.println(headStr);
                        continue;

                    } else { // 非表头（注意读取的时候要注意单元格内数据的格式，要使用正确的读取方法）
                        XSSFRow row = sheet.getRow(rowNum);
//                        int id = (int) row.getCell(0).getNumericCellValue();
//                        String name = row.getCell(1).getStringCellValue();
//                        int age = (int) row.getCell(2).getNumericCellValue();
//                        String addr = row.getCell(3).getStringCellValue();
//                        String rowContent = String.format("%s\t%s\t%s\t%s", id, name, age, addr);
//                        System.out.println(rowContent);
                        double date = row.getCell(1).getNumericCellValue();
                        try{
//                            System.out.println(NumberToTextConverter.toText(date));
                            String dateStr = NumberToTextConverter.toText(date);
                            if(dateStr.length() > 8) {
                                System.out.println(dateStr);
                            }
                            sdf.parse(NumberToTextConverter.toText(date));
                        }catch (Exception e){
                            System.out.println(NumberToTextConverter.toText(date) +"row ==>" + rowNum );
                            e.printStackTrace();
                        }
//                        System.out.println(NumberToTextConverter.toText(date));

                    }
                }
            }

        }catch (IOException e){
            e.printStackTrace();
        }



    }

    /**
     * 补全卷内目录信息
     * @param archiveExcel
     * @param fileExcel
     */
    public static void fillInfo(String archiveExcel, String fileExcel){

    }

    public static void readArchiveNo(String archiveExcel){
        List<String> archiveNos = new ArrayList<>();
        try(BufferedInputStream inputStream = new BufferedInputStream(new FileInputStream(archiveExcel))){
            // 直接传入输入流即可，此时excel就已经解析了
            try(XSSFWorkbook workbook = new XSSFWorkbook(inputStream)){
                // 选择要处理的sheet名称
                XSSFSheet sheet = workbook.getSheetAt(0);

                // 第一行表头，单独处理

                // 迭代遍历sheet剩余的每一行
                for (int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                    if (rowNum == 0) { // 读取第一行（表头）
//                        XSSFRow head = sheet.getRow(rowNum);
//                        String headColumn_1 = head.getCell(0).getStringCellValue();
//                        String headColumn_2 = head.getCell(1).getStringCellValue();
//                        String headColumn_3 = head.getCell(2).getStringCellValue();
//                        String headColumn_4 = head.getCell(3).getStringCellValue();
//                        String headStr = String.format("%s\t%s\t%s\t%s", headColumn_1, headColumn_2, headColumn_3, headColumn_4);
//                        System.out.println(headStr);
                        continue;

                    } else { // 非表头（注意读取的时候要注意单元格内数据的格式，要使用正确的读取方法）
                        XSSFRow row = sheet.getRow(rowNum);
//                        int id = (int) row.getCell(0).getNumericCellValue();
//                        String name = row.getCell(1).getStringCellValue();
//                        int age = (int) row.getCell(2).getNumericCellValue();
//                        String addr = row.getCell(3).getStringCellValue();
//                        String rowContent = String.format("%s\t%s\t%s\t%s", id, name, age, addr);
//                        System.out.println(rowContent);

                        try{
//                            System.out.println(NumberToTextConverter.toText(date));
                            String stringCellValue = row.getCell(5).getStringCellValue();
                            if(!archiveNos.contains(stringCellValue)){
                                archiveNos.add(stringCellValue);
                            }
                        }catch (Exception e){
//                            System.out.println(NumberToTextConverter.toText(date) +"row ==>" + rowNum );
                            e.printStackTrace();
                        }
//                        System.out.println(NumberToTextConverter.toText(date));

                    }
                }
            }
            for (String archiveNo : archiveNos) {
                System.out.print(archiveNo+",");
            }
        }catch (IOException e){
            e.printStackTrace();
        }
    }

}
